using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Windows.Forms.Design;
using System.Configuration;
using System.Threading;
using MySql.Data.MySqlClient;

namespace Loader
{
    public partial class FormMain : Form
    {
        #region Static variables
        static readonly string SQL_TRUNCATE_QUERY = "Truncate table chungtu; truncate table quay; truncate table nhacungcap;";// Truncate table nhanvien; Truncate table nhanvien_nhomhang;Truncate table nhanvien_quay;Truncate table nhanvien_quyen;

        static readonly string SQL_INSERT_PROVIDERS = "INSERT INTO nhacungcap (mancc,tenncc,diachi,quan,sodt,email,nguoidaidien,mstvat,sotk,tennganhang,thoihantt,thoidiemtt,nhomhanghoa, priority) VALUES (@mancc,@tenncc,@diachi,@quan,@sodt,@email,@nguoidaidien,@mstvat,@sotk,@tennganhang,@thoihantt,@thoidiemtt,@nhomhanghoa,@priority)";

        static readonly string SQL_INSERT_COUNTERS = "INSERT INTO quay(maquay, kyhieu, tenquay) VALUES (@maquay, @kyhieu, @tenquay)";

        static readonly string SQL_INSERT_BILL = "insert into chungtu( maCT,  soCT,  ngayCT,  doanhSo,  thueSuat,  thue,  phaiChi,  phaiThu,  ngayNhap,  maNCC,  maQuay,  daTT,  ngayTT,  tttheoTH,  thoihanTT,  thueNop,  thueKhauTru,  isChi,  loaiCt,  ghichu,  nvID) VALUES (@maCT, @soCT, @ngayCT, @doanhSo, @thueSuat, @thue, @phaiChi, @phaiThu, @ngayNhap, @maNCC, @maQuay, @daTT, @ngayTT, @tttheoTH, @thoihanTT, @thueNop, @thueKhauTru, @isChi, @loaiCt, @ghichu, @nvID)";

        public static string TRADE_DB_NAME = ConfigurationManager.AppSettings["BILL_DBNAME"];
        public static string TRADE_DB_HOST = ConfigurationManager.AppSettings["BILL_DBHOST"];
        public static string TRADE_DB_USER = ConfigurationManager.AppSettings["BILL_USER"];
        public static string TRADE_DB_PASSWORD = ConfigurationManager.AppSettings["BILL_PASSWORD"];
        public static int NUM_THREADS = Convert.ToInt32(ConfigurationManager.AppSettings["NUM_THREADS"]);
        static string SQL_CONN = null;
        #endregion

        DatabaseCreation formentry = null;

        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        private static short[] ckttList = { 5, 15, 25 };
        private static string[] loaiCTList = { "HDN", "HDX", "NOVAT" };
        private static int numProviders = 0;
        private static int numCounters = 0;

        /// <summary>
        /// Simple constructor, gets default settings from the config file.
        /// </summary>
        public FormMain()
        {
            InitializeComponent();
            dbName.Text = TRADE_DB_NAME;
            UserName.Text = TRADE_DB_USER;
            Password.Text = TRADE_DB_PASSWORD;
            dbHost.Text = TRADE_DB_HOST;
            formentry = new DatabaseCreation(this);
        }

        private void Form1_Load(object sender, EventArgs evArgs)
        {
            UserName.Text = TRADE_DB_USER;
            Password.Text = TRADE_DB_PASSWORD;
            dbHost.Text = TRADE_DB_HOST;
            checkBoxSaltedHash.Checked = true;
            tipSaltedHash.SetToolTip(checkBoxSaltedHash, "One-way password encryption in database table when checked.");
        }

        /// <summary>
        /// Will launch our create database form.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="evArgs"></param>
        private void buttonCreate_Click_1(object sender, EventArgs evArgs)
        {
            DialogResult reply = System.Windows.Forms.MessageBox.Show("Make sure you have specified a MySQL Server Username with database creation and root rights before proceeding!", "Database Creation Information", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            if (reply == DialogResult.OK)
            {
                TRADE_DB_HOST = dbHost.Text;
                TRADE_DB_USER = UserName.Text;
                TRADE_DB_PASSWORD = Password.Text;
                if (formentry == null || formentry.IsDisposed)
                    formentry = new DatabaseCreation(this);
                formentry.DBName.Text = dbName.Text;
                DatabaseCreation.DBServer = dbHost.Text;
                DatabaseCreation.db_PASSWORD = Password.Text;
                DatabaseCreation.db_USER = UserName.Text;
                formentry.Show();
            }
            else
                return;
        }

        /// <summary>
        /// Will return counts from tables.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="evArgs"></param>
        public void button2_Click(object sender, EventArgs evArgs)
        {
            try
            {
                SQL_CONN = "server=" + dbHost.Text + ";Database=" + dbName.Text + ";user id=" + UserName.Text + ";password=" + Password.Text + ";Connection Timeout=30;";
                getCounts(SQL_CONN);
            }
            catch (Exception e)
            {
                showMBox(this, "Processing Error! Exception is: " + e.Message, "Loader Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
        }

        /// <summary>
        /// Deletes all data above the default identity seeds set (100000000); and new users with id <> 'uid:x';
        /// This helps reset large data sets between benchmark runs. 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="evArgs"></param>
        private void button1_Click(object sender, EventArgs evArgs)
        {
            SQL_CONN = "server=" + dbHost.Text + ";Database=" + dbName.Text + ";user id=" + UserName.Text + ";password=" + Password.Text + ";Connection Timeout=30;";
            DeleteUsersSql.Text = "Deleting Data...";
            buttonClean.Enabled = false;
            buttonLoad.Enabled = false;
            button2.Enabled = false;
            buttonCreate.Enabled = false;
            processDelegate process = new processDelegate(Process);
            process.BeginInvoke(true, SQL_CONN, maxProviders.Text, maxBills.Text, maxCounters.Text, null, new object[] { dbHost.Text });
            return;
        }

        /// <summary>
        /// Loads the database.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="evArgs"></param>
        private void buttonLoad_Click(object sender, EventArgs evArgs)
        {
            int numQuotes = Convert.ToInt32(maxBills.Text);
            if (numQuotes < 0)
                System.Windows.Forms.MessageBox.Show(this, "Please enter at least 1 quotes to load.", "Minimum Quotes Warning");
            else
            {
                DialogResult reply = System.Windows.Forms.MessageBox.Show(this, "Warning! This command will delete all data in the database before starting the new database load! Do you want to continue?", "Loader Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                if (reply == DialogResult.Yes)
                {
                    LOADDB.Text = "Loading Data...";
                    SQL_CONN = "server=" + dbHost.Text + ";Database=" + dbName.Text + ";user id=" + UserName.Text + ";password=" + Password.Text + ";Connection Timeout=30;";
                    buttonClean.Enabled = false;
                    buttonLoad.Enabled = false;
                    button2.Enabled = false;
                    buttonCreate.Enabled = false;
                    checkBoxSaltedHash.Enabled = false;
                    processDelegate process = new processDelegate(Process);
                    process.BeginInvoke(false, SQL_CONN, maxProviders.Text, maxBills.Text, maxCounters.Text, null, new object[] { dbHost.Text });
                }
            }
            return;
        }

        /// <summary>
        /// Runs on background thread to keep UI responsive.  Truncates tables, then loads each table with
        /// user specified counts.
        /// </summary>
        /// <param name="clean"></param>
        /// <param name="SQL_CONN"></param>
        /// <param name="maxUsers"></param>
        /// <param name="maxQuotes"></param>
        /// <param name="numHoldings"></param>
        delegate void processDelegate(bool clean, string SQL_CONN, string maxProviders, string maxBills, string maxCounters);
        private void Process(bool clean, string SQL_CONN, string maxProviders, string maxBills, string maxCounters)
        {
            try
            {
                if (clean)
                {
                    cleanDB(SQL_CONN);
                    getCounts(SQL_CONN);
                    showStatus(true, "Done!");
                }
                else
                {
                    getCounts(SQL_CONN);
                    truncateSQLData(SQL_CONN);
                    getCounts(SQL_CONN);

                    List<ProviderLoadClass> providerLoadList = new List<ProviderLoadClass>();
                    List<Thread> threadList = new List<Thread>();
                    numProviders = Convert.ToInt32(maxProviders);
                    for (int i = 0; i < NUM_THREADS; i++)
                    {
                        int start = (numProviders / NUM_THREADS) * i;
                        int end = start + (numProviders / NUM_THREADS) - 1;
                        if (i.Equals(NUM_THREADS - 1))
                        {
                            int extra = numProviders % NUM_THREADS;
                            end += extra;
                        }
                        ProviderLoadClass providerLoad = new ProviderLoadClass(SQL_CONN, start, end, this);
                        providerLoadList.Add(providerLoad);
                        Thread t = new Thread(new ThreadStart(providerLoadList[i].startLoad));
                        threadList.Add(t);
                    }
                    foreach (Thread t in threadList)
                    {
                        try
                        {
                            t.Start();
                        }
                        catch
                        {
                            throw;
                        }
                    }
                    foreach (Thread t in threadList)
                    {
                        if (t.ThreadState != ThreadState.Stopped)
                            t.Join();
                    }
                    getCounts(SQL_CONN);

                    List<CounterLoadClass> counterLoadList = new List<CounterLoadClass>();
                    threadList = new List<Thread>();
                    numCounters = Convert.ToInt32(maxCounters);
                    for (int i = 0; i < NUM_THREADS; i++)
                    {
                        int start = (numCounters / NUM_THREADS) * i;
                        int end = start + (numCounters / NUM_THREADS) - 1;
                        if (i.Equals(NUM_THREADS - 1))
                        {
                            int extra = numCounters % NUM_THREADS;
                            end += extra;
                        }
                        CounterLoadClass billLoad = new CounterLoadClass(SQL_CONN, start, end, this);
                        counterLoadList.Add(billLoad);
                        Thread t = new Thread(new ThreadStart(counterLoadList[i].startLoad));
                        threadList.Add(t);
                    }
                    foreach (Thread t in threadList)
                    {
                        try
                        {
                            t.Start();
                        }
                        catch
                        {
                            throw;
                        }
                    }
                    foreach (Thread t in threadList)
                    {
                        if (t.ThreadState != ThreadState.Stopped)
                            t.Join();
                    }
                    getCounts(SQL_CONN);

                    List<BillLoadClass> billLoadList = new List<BillLoadClass>();
                    threadList = new List<Thread>();
                    int numBills = Convert.ToInt32(maxBills);
                    for (int i = 0; i < NUM_THREADS; i++)
                    {
                        int start = (numBills / NUM_THREADS) * i;
                        int end = start + (numBills / NUM_THREADS) - 1;
                        if (i.Equals(NUM_THREADS - 1))
                        {
                            int extra = numBills % NUM_THREADS;
                            end += extra;
                        }
                        BillLoadClass billLoad = new BillLoadClass(SQL_CONN, start, end, this);
                        billLoadList.Add(billLoad);
                        Thread t = new Thread(new ThreadStart(billLoadList[i].startLoad));
                        threadList.Add(t);
                    }
                    foreach (Thread t in threadList)
                    {
                        try
                        {
                            t.Start();
                        }
                        catch
                        {
                            throw;
                        }
                    }
                    foreach (Thread t in threadList)
                    {
                        if (t.ThreadState != ThreadState.Stopped)
                            t.Join();
                    }
                    getCounts(SQL_CONN);

                }
                enableButtons();
                return;
            }
            catch (Exception e)
            {
                showMBox(this, "Processing Error! Exception is: " + e.Message, "Loader Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                enableButtons();
                return;
            }
        }

        public class ProviderLoadClass
        {
            private static string _SQL_CONN;
            private int _start = -1;
            private int _end = -1;
            private FormMain _form;
            public static int Counter = 0;

            public ProviderLoadClass(string SQL_CONN, int start, int end, FormMain form)
            {
                _start = start;
                _end = end;
                _form = form;
                _SQL_CONN = SQL_CONN;
            }

            public void startLoad()
            {
                _form.loadProviderSQL(_SQL_CONN, _start, _end, ref Counter);
            }
        }

        public class CounterLoadClass
        {
            private static string _SQL_CONN;
            private int _start = -1;
            private int _end = -1;
            private FormMain _form;
            public static int Counter = 0;

            public CounterLoadClass(string SQL_CONN, int start, int end, FormMain form)
            {
                _start = start;
                _end = end;
                _form = form;
                _SQL_CONN = SQL_CONN;
            }

            public void startLoad()
            {
                _form.loadCounterSQL(_SQL_CONN, _start, _end, ref Counter);
            }
        }


        public class BillLoadClass
        {
            private static string _SQL_CONN;
            private int _start = -1;
            private int _end = -1;
            private FormMain _form;
            public static int Counter = 0;

            public BillLoadClass(string SQL_CONN, int start, int end, FormMain form)
            {
                _start = start;
                _end = end;
                _form = form;
                _SQL_CONN = SQL_CONN;
            }

            public void startLoad()
            {
                _form.loadBillSQL(_SQL_CONN, _start, _end, ref Counter);
            }
        }

        public class AccountLoadClass
        {
            private int _startNumber = -1;
            private int _endNumber = -1;
            private int _numQuotes = -1;
            private int _numHoldings = -1;
            private static string _SQL_CONN;
            private FormMain _form;
            public static int CounterAccount = 0;
            public static int CounterOrders = 0;


            public AccountLoadClass(string SQL_CONN, int start, int end, FormMain form, int numQuotes, int numHoldings)
            {
                _startNumber = start;
                _endNumber = end;
                _form = form;
                _SQL_CONN = SQL_CONN;
                _numQuotes = numQuotes;
                _numHoldings = numHoldings;
            }

            public void startLoad()
            {
            }
        }

        /// <summary>
        /// Our progress counter.
        /// </summary>
        /// <param name="_progress"></param>
        /// <param name="count"></param>
        delegate void ShowProgressDelegate(int _progress, int count);
        void ShowProgress(int _progress, int count)
        {
            // Make sure we're on the right thread
            if (lblOtherRows.InvokeRequired == false)
            {
                switch (_progress)
                {
                    case 0: { lblImportRows.Text = count.ToString(); lblImportRows.Refresh(); break; }
                    case 1: { lblExportRows.Text = count.ToString(); lblExportRows.Refresh(); break; }
                    case 2: { lblOtherRows.Text = count.ToString(); lblOtherRows.Refresh(); break; }
                    case 3: { lblProviders.Text = count.ToString(); lblProviders.Refresh(); break; }
                    case 4: { lblCounters.Text = count.ToString(); lblCounters.Refresh(); break; }
                }
            }
            else
            {
                // Show progress asynchronously
                ShowProgressDelegate showprogress = new ShowProgressDelegate(ShowProgress);
                this.BeginInvoke(showprogress, new object[] { _progress, count });
            }
            return;
        }

        /// <summary>
        /// Refrehses status labels.
        /// </summary>
        /// <param name="clean"></param>
        /// <param name="status"></param>
        delegate void showStatusDelegate(bool clean, string status);
        private void showStatus(bool clean, string status)
        {
            if (LOADDB.InvokeRequired == false)
            {
                if (!clean)
                {
                    LOADDB.Text = status;
                    LOADDB.Refresh();
                }
                else
                {
                    DeleteUsersSql.Text = status;
                    DeleteUsersSql.Refresh();
                }
            }
            else
            {
                showStatusDelegate showstatus = new showStatusDelegate(showStatus);
                this.BeginInvoke(showstatus, new object[] { clean, status });
            }
            return;
        }

        /// <summary>
        /// Invokes message boxes on errors and/or user information.
        /// </summary>
        /// <param name="owner"></param>
        /// <param name="message"></param>
        /// <param name="caption"></param>
        /// <param name="buttons"></param>
        /// <param name="icon"></param>
        delegate void showMBoxDelegate(IWin32Window owner, string message, string caption, MessageBoxButtons buttons, MessageBoxIcon icon);
        private void showMBox(IWin32Window owner, string message, string caption, MessageBoxButtons buttons, MessageBoxIcon icon)
        {
            if (DeleteUsersSql.InvokeRequired == false)
            {
                System.Windows.Forms.MessageBox.Show(owner, message, caption, buttons, icon);
            }
            else
            {
                showMBoxDelegate showmbox = new showMBoxDelegate(showMBox);
                this.BeginInvoke(showmbox, new object[] { owner, message, caption, buttons, icon });
            }
            return;
        }

        /// <summary>
        /// Used to re-enable buttons when a load or delete finishes
        /// </summary>
        delegate void enableButtonsDelegate();
        private void enableButtons()
        {
            if (buttonClean.InvokeRequired == false)
            {
                buttonClean.Enabled = true;
                buttonLoad.Enabled = true;
                button2.Enabled = true;
                buttonCreate.Enabled = true;
                checkBoxSaltedHash.Enabled = true;
                DeleteUsersSql.Text = "Ready to Start!";
                LOADDB.Text = "Ready to Start!";
            }
            else
            {
                enableButtonsDelegate enablebuttons = new enableButtonsDelegate(enableButtons);
                this.BeginInvoke(enablebuttons, null);
            }
            return;
        }

        /// <summary>
        /// This method will delete data points with sequence numbers above the pre-set default starting identity seed number (100000000).
        /// Also, deletes any new users added by benchmark runs where the user profile ID <> 'uid:x'; since we do not want to delete
        /// our base data load.  Assumes benchmark scripts add new users with a different user id pattern, like 'user:x';
        /// </summary>
        /// <param name="SQL_CONN"></param>
        private void cleanDB(string SQL_CONN)
        {
            MySqlConnection sqlConnection = new MySqlConnection(SQL_CONN);
            try
            {
                sqlConnection.Open();
                MySqlCommand sqlCommand = new MySqlCommand(SQL_TRUNCATE_QUERY, sqlConnection);
                sqlCommand.CommandTimeout = 1200;
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                return;
            }
            catch (Exception e)
            {
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Gets table counts and refreshes labels with data.
        /// </summary>
        /// <param name="SQL_CONN"></param>
        delegate void getCountsDelegate(string SQL_CONN);
        public void getCounts(string SQL_CONN)
        {
            if (!lblImportRows.InvokeRequired)
            {
                MySqlConnection sqlConnection = new MySqlConnection(SQL_CONN);
                try
                {
                    int count = 0;
                    sqlConnection.Open();
                    MySqlCommand sqlCommand = new MySqlCommand("SELECT COUNT(*) FROM chungtu WHERE LoaiCT='HDN'", sqlConnection);
                    count = Convert.ToInt32(sqlCommand.ExecuteScalar());
                    lblImportRows.Text = count.ToString();

                    sqlCommand.CommandText = "SELECT COUNT(*) FROM chungtu WHERE LoaiCT='HDX'";
                    count = Convert.ToInt32(sqlCommand.ExecuteScalar());
                    lblExportRows.Text = count.ToString();

                    sqlCommand.CommandText = "SELECT COUNT(*) FROM chungtu WHERE (LoaiCT<>'HDN') AND (LoaiCT<>'HDX')";
                    count = Convert.ToInt32(sqlCommand.ExecuteScalar());
                    lblOtherRows.Text = count.ToString();

                    sqlCommand.CommandText = "SELECT COUNT(*) FROM nhacungcap";
                    count = Convert.ToInt32(sqlCommand.ExecuteScalar());
                    lblProviders.Text = count.ToString();

                    sqlCommand.CommandText = "SELECT COUNT(*) FROM quay";
                    count = Convert.ToInt32(sqlCommand.ExecuteScalar());
                    lblCounters.Text = count.ToString();

                    sqlCommand.CommandText = "SELECT COUNT(*) FROM nhanvien";
                    count = Convert.ToInt32(sqlCommand.ExecuteScalar());
                    lblUsers.Text = count.ToString();
                    sqlConnection.Close();
                    return;
                }
                catch (Exception e)
                {
                    if (sqlConnection.State == ConnectionState.Open)
                        sqlConnection.Close();
                    throw new Exception(e.Message);
                }
            }
            else
            {
                // Show progress asynchronously
                getCountsDelegate getcounts = new getCountsDelegate(getCounts);
                this.BeginInvoke(getcounts, new object[] { SQL_CONN });
            }
            return;
        }

        /// <summary>
        /// Deletes (by table truncation) all data in all database tables.
        /// </summary>
        /// <param name="SQL_CONN"></param>
        private void truncateSQLData(string SQL_CONN)
        {
            MySqlConnection sqlConnection = new MySqlConnection(SQL_CONN);
            try
            {
                sqlConnection.Open();
                MySqlCommand sqlCommand = new MySqlCommand(SQL_TRUNCATE_QUERY, sqlConnection);
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                return;
            }
            catch (Exception e)
            {
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Returns cached Providers insert query params.
        /// </summary>
        /// <returns></returns>
        private static MySqlParameter[] GetInsertProviderParameters()
        {
            // Get the parameters from the cache
            MySqlParameter[] parms = GetCacheParameters(SQL_INSERT_PROVIDERS);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new MySqlParameter[] {
        			  new MySqlParameter("@maNCC", MySqlDbType.VarChar, 20),
                      new MySqlParameter("@TenNCC", MySqlDbType.VarChar, 200),
                      new MySqlParameter("@DiaChi", MySqlDbType.VarChar,300),
                      new MySqlParameter("@Quan", MySqlDbType.VarChar),
                      new MySqlParameter("@SoDT", MySqlDbType.VarChar),
                      new MySqlParameter("@Email", MySqlDbType.VarChar),
                      new MySqlParameter("@NguoiDaiDien", MySqlDbType.VarChar, 50),
                      new MySqlParameter("@MSTVAT", MySqlDbType.VarChar, 15),
                      new MySqlParameter("@SoTK", MySqlDbType.VarChar, 45),
                      new MySqlParameter("@TenNganHang", MySqlDbType.VarChar, 45),
                      new MySqlParameter("@ThoiHanTT", MySqlDbType.Int16),
                      new MySqlParameter("@ThoiDiemTT", MySqlDbType.Int16),
		              new MySqlParameter("@NhomHangHoa", MySqlDbType.Int32),
		              new MySqlParameter("@Priority", MySqlDbType.Int32)
                };

                // Add the parameters to the cached
                CacheParameters(FormMain.SQL_INSERT_PROVIDERS, parms);
            }
            return parms;
        }

        /// <summary>
        /// Performs an actual insert into PROVIDESR table.
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="low"></param>
        /// <param name="open1"></param>
        /// <param name="volume"></param>
        /// <param name="price"></param>
        /// <param name="high"></param>
        /// <param name="companyName"></param>
        /// <param name="symbol"></param>
        /// <param name="change1"></param>
        private void insertProviders(MySqlConnection sqlConnection, string mancc, string tenncc, string diachi, string quan, string sodt, string email, string nguoidaidien, string mstvat, string sotk, string tennganhang, short thoihantt, short thoidiemtt, int nhomhanghoa, int priority)
        {
            try
            {
                MySqlParameter[] parms = GetInsertProviderParameters();
                MySqlCommand sqlCommand = new MySqlCommand(SQL_INSERT_PROVIDERS);
                sqlCommand.Connection = sqlConnection;
                parms[0].Value = mancc;
                parms[1].Value = tenncc;
                parms[2].Value = diachi;
                parms[3].Value = quan;
                parms[4].Value = sodt;
                parms[5].Value = email;
                parms[6].Value = nguoidaidien;
                parms[7].Value = mstvat;
                parms[8].Value = sotk;
                parms[9].Value = tennganhang;
                parms[10].Value = thoihantt;
                parms[11].Value = thoidiemtt;
                parms[12].Value = nhomhanghoa;
                parms[13].Value = priority;
                PrepareCommand(sqlCommand, sqlConnection, null, CommandType.Text, SQL_INSERT_PROVIDERS, parms);
                sqlCommand.ExecuteNonQuery();
                return;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Method that loops and inserts a new quote up to the user-defined max quote setting.
        /// </summary>
        /// <param name="SQL_CONN"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="count"></param>
        /// <param name="doOnce"></param>
        private void loadProviderSQL(string SQL_CONN, int start, int end, ref int count)
        {
            MySqlConnection sqlConnection = new MySqlConnection(SQL_CONN);
            DateTime startDate = new DateTime(2003, 1, 1);
            Random gen = new Random();

            try
            {
                sqlConnection.Open();
                for (int i = start; i <= end; i++)
                {
                    if (i % 50 == 0)
                        ShowProgress(0, count);
                    string mancc = "NCC" + i.ToString();
                    string tenNCC = "NCC" + i.ToString();
                    string diachi = string.Empty;
                    string email = string.Empty;
                    string nguoidaidien = string.Empty;
                    string mstvat = RandomString(15, true);
                    string sotk = RandomString(45, true);
                    string tennganhang = string.Empty;
                    string quan = string.Empty;
                    string soDT = gen.Next(1000000, 9999999).ToString();
                    short thoihanTT = ckttList[gen.Next(0, 2)];
                    short thoidiemTT = Convert.ToInt16(gen.Next(0, 20));
                    int nhomhanghoa = gen.Next(1, 10);
                    int priority = gen.Next(0, 10);

                    insertProviders(sqlConnection, mancc, tenNCC, diachi, quan, soDT, email, nguoidaidien, mstvat, sotk, tennganhang, thoihanTT, thoidiemTT, nhomhanghoa, priority);
                    count++;
                }

                sqlConnection.Close();
                return;
            }
            catch (Exception e)
            {
                if (sqlConnection.State == ConnectionState.Closed)
                    sqlConnection.Close();
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Returns cached Counters insert query params.
        /// </summary>
        /// <returns></returns>
        private static MySqlParameter[] GetInsertCounterParameters()
        {
            // Get the parameters from the cache
            MySqlParameter[] parms = GetCacheParameters(SQL_INSERT_COUNTERS);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new MySqlParameter[] {
        			  new MySqlParameter("@maquay", MySqlDbType.VarChar, 20),
                      new MySqlParameter("@kyHieu", MySqlDbType.VarChar, 20),
                      new MySqlParameter("@tenquay", MySqlDbType.VarChar,200)
                };

                // Add the parameters to the cached
                CacheParameters(FormMain.SQL_INSERT_COUNTERS, parms);
            }
            return parms;
        }

        /// <summary>
        /// Performs an actual insert into Counters table.
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="low"></param>
        /// <param name="open1"></param>
        /// <param name="volume"></param>
        /// <param name="price"></param>
        /// <param name="high"></param>
        /// <param name="companyName"></param>
        /// <param name="symbol"></param>
        /// <param name="change1"></param>
        private void insertCounters(MySqlConnection sqlConnection, string maquay, string kyhieu, string tenquay)
        {
            try
            {
                MySqlParameter[] parms = GetInsertCounterParameters();
                MySqlCommand sqlCommand = new MySqlCommand(SQL_INSERT_COUNTERS);
                sqlCommand.Connection = sqlConnection;

                parms[0].Value = maquay;
                parms[1].Value = kyhieu;
                parms[2].Value = tenquay;

                PrepareCommand(sqlCommand, sqlConnection, null, CommandType.Text, SQL_INSERT_COUNTERS, parms);
                sqlCommand.ExecuteNonQuery();
                return;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Method that loops and inserts a new quote up to the user-defined max quote setting.
        /// </summary>
        /// <param name="SQL_CONN"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="count"></param>
        /// <param name="doOnce"></param>
        private void loadCounterSQL(string SQL_CONN, int start, int end, ref int count)
        {
            MySqlConnection sqlConnection = new MySqlConnection(SQL_CONN);
            try
            {
                sqlConnection.Open();
                for (int i = start; i <= end; i++)
                {
                    if (i % 50 == 0)
                        ShowProgress(0, count);
                    string maquay = "QUAY" + i.ToString();
                    string kyhieu = "QUAY" + i.ToString();
                    string tenquay = "TEN QUAY " + i.ToString();

                    insertCounters(sqlConnection, maquay, kyhieu, tenquay);
                    count++;
                }

                sqlConnection.Close();
                return;
            }
            catch (Exception e)
            {
                if (sqlConnection.State == ConnectionState.Closed)
                    sqlConnection.Close();
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Returns cached quote insert query params.
        /// </summary>
        /// <returns></returns>
        private static MySqlParameter[] GetInsertBillParameters()
        {
            // Get the parameters from the cache
            MySqlParameter[] parms = GetCacheParameters(SQL_INSERT_BILL);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new MySqlParameter[] {
        			  new MySqlParameter("@maCT", MySqlDbType.VarChar, 20),
                      new MySqlParameter("@soCT", MySqlDbType.VarChar, 45),
                      new MySqlParameter("@ngayCT", MySqlDbType.DateTime),
                      new MySqlParameter("@doanhSo", MySqlDbType.Int64),
                      new MySqlParameter("@thueSuat", MySqlDbType.Int32),
                      new MySqlParameter("@thue", MySqlDbType.Int64),
                      new MySqlParameter("@phaiChi", MySqlDbType.Int64, 14),
                      new MySqlParameter("@phaiThu", MySqlDbType.Int64, 14),
                      new MySqlParameter("@ngayNhap", MySqlDbType.DateTime, 14),
                      new MySqlParameter("@maNCC", MySqlDbType.VarChar, 14),
                      new MySqlParameter("@maQuay", MySqlDbType.VarChar, 14),
		              new MySqlParameter("@daTT", MySqlDbType.Byte),
                      new MySqlParameter("@NgayTT", MySqlDbType.DateTime),
                      new MySqlParameter("@TTTheoTH", MySqlDbType.Byte),
                      new MySqlParameter("@thoiHanTT", MySqlDbType.Int32),
                      new MySqlParameter("@thueNop", MySqlDbType.Int64),
                      new MySqlParameter("@thueKhauTru", MySqlDbType.Int64),
                      new MySqlParameter("@isChi", MySqlDbType.Byte),
                      new MySqlParameter("@loaiCT", MySqlDbType.VarChar,20),
                      new MySqlParameter("@ghiChu", MySqlDbType.VarChar,255),
                      new MySqlParameter("@NVID", MySqlDbType.Int32)
                };

                // Add the parameters to the cached
                CacheParameters(FormMain.SQL_INSERT_BILL, parms);
            }
            return parms;
        }

        /// <summary>
        /// Performs an actual insert into QUOTE table.
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="low"></param>
        /// <param name="open1"></param>
        /// <param name="volume"></param>
        /// <param name="price"></param>
        /// <param name="high"></param>
        /// <param name="companyName"></param>
        /// <param name="symbol"></param>
        /// <param name="change1"></param>
        private void insertBill(MySqlConnection sqlConnection, string maCT, string soCT, DateTime ngayCT, long doanhSo, int thueSuat, long thue, long phaiChi, long phaiThu, DateTime ngayNhap, string maNCC, string maQuay, byte daTT, DateTime ngayTT, byte tttheoTH, int thoihanTT, long thueNop, long thueKhauTru, byte isChi, string loaiCt, string ghichu, int nvID)
        {
            try
            {
                MySqlParameter[] parms = GetInsertBillParameters();
                MySqlCommand sqlCommand = new MySqlCommand(SQL_INSERT_BILL);
                sqlCommand.Connection = sqlConnection;
                parms[0].Value = maCT;
                parms[1].Value = soCT;
                parms[2].Value = ngayCT;
                parms[3].Value = doanhSo;
                parms[4].Value = thueSuat;
                parms[5].Value = thue;
                parms[6].Value = phaiChi;
                parms[7].Value = phaiThu;
                parms[8].Value = ngayNhap;
                parms[9].Value = maNCC;
                parms[10].Value = maQuay;
                parms[11].Value = daTT;
                parms[12].Value = ngayTT;
                parms[13].Value = tttheoTH;
                parms[14].Value = thoihanTT;
                parms[15].Value = thueNop;
                parms[16].Value = thueKhauTru;
                parms[17].Value = isChi;
                parms[18].Value = loaiCt;
                parms[19].Value = ghichu;
                parms[20].Value = nvID;
                PrepareCommand(sqlCommand, sqlConnection, null, CommandType.Text, SQL_INSERT_BILL, parms);
                sqlCommand.ExecuteNonQuery();
                return;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// Method that loops and inserts a new quote up to the user-defined max quote setting.
        /// </summary>
        /// <param name="SQL_CONN"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="count"></param>
        /// <param name="doOnce"></param>
        private void loadBillSQL(string SQL_CONN, int start, int end, ref int count)
        {
            MySqlConnection sqlConnection = new MySqlConnection(SQL_CONN);
            DateTime startDate = new DateTime(2009, 1, 1);

            try
            {
                sqlConnection.Open();
                for (int i = start; i <= end; i++)
                {
                    if (i % 50 == 0)
                        ShowProgress(0, count);
                    string maCt = "AutoGen_MaCT_" + count.ToString();
                    string soCt = "AutoGen_SoCT_" + count.ToString();
                    Random gen = new Random();
                    int range = ((TimeSpan)(DateTime.Today - startDate)).Days;
                    int rValue = gen.Next(range);
                    DateTime NgayCT = startDate.AddDays(rValue);
                    long doanhSo = rValue * 10;
                    int thueSuat = rValue % 7;
                    long thue = (thueSuat * doanhSo) / 100;
                    string loaiCT = loaiCTList[gen.Next(0, loaiCTList.Length - 1)];
                    int thoihantt = gen.Next(1, 30);

                    byte isChi = Convert.ToByte(gen.Next(0, 2));
                    long phaiChi = 0;
                    long phaiThu = 0;
                    if (isChi.Equals(1))
                        phaiChi = doanhSo;
                    else
                        phaiThu = doanhSo;

                    // A random day within 2 months after bill date
                    DateTime ngaynhap = NgayCT.AddDays(gen.Next(0, 30));

                    string maNCC = "NCC" + (i % numProviders).ToString();
                    string maQuay = "QUAY" + (i % numCounters).ToString();

                    byte daTT = Convert.ToByte((gen.Next(0, 10).Equals(10)) ? 1 : 0);
                    DateTime ngayTT = (daTT.Equals(1)) ? NgayCT.AddDays(gen.Next(0, 60)) : DateTime.Today;
                    byte TTtheoTH = Convert.ToByte((gen.Next(0, 4).Equals(1)) ? 0 : 1);
                    string ghichu = string.Empty;
                    int nvid = gen.Next(0, 10);
                    long thuenop = 0;
                    long thuekhautru = 0;

                    insertBill(sqlConnection, maCt, soCt, NgayCT, doanhSo, thueSuat, thue, phaiChi, phaiThu, ngaynhap, maNCC, maQuay, daTT, ngayTT, TTtheoTH, thoihantt, thuenop, thuekhautru, isChi, loaiCT, ghichu, nvid);
                    count++;
                }

                sqlConnection.Close();
                return;
            }
            catch (Exception e)
            {
                if (sqlConnection.State == ConnectionState.Closed)
                    sqlConnection.Close();
                throw new Exception(e.Message);
            }
        }


        /// <summary>
        /// Inserts a set of SQL params into the cache.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="cmdParms"></param>
        private static void CacheParameters(string cacheKey, params MySqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
            return;
        }

        /// <summary>
        /// Returns a set of cached params if they exist based on the lookup key.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        private static MySqlParameter[] GetCacheParameters(string cacheKey)
        {
            MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Binds all params and prepares command for execution.
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sqlConnection"></param>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection sqlConnection, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {

            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            cmd.Connection = sqlConnection;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                for (int i = 0; i < cmdParms.Length; i++)
                {
                    MySqlParameter parm = (MySqlParameter)cmdParms[i];
                    cmd.Parameters.Add(parm);
                }
            }
            return;
        }

        private string RandomString(int size, bool lowerCase)
        {
            StringBuilder builder = new StringBuilder();
            Random random = new Random();
            char ch;
            for (int i = 0; i < size; i++)
            {
                ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
                builder.Append(ch);
            }
            if (lowerCase)
                return builder.ToString().ToLower();
            return builder.ToString();
        }
    }
}